<?php

class Reminder_model extends CI_Model {
    
    public function getReminderByDate($strtotime,$userid,$ispickup=FALSE){
//        $data['noticedate'] = $strtotime;
//        if($userid*1 > 0){
//            $query = $this->db->get_where('employee', array('userid' => $userid));
//            $employee =  $query->row_array();
//            if($employee['department'] == 3){
//                $data['ispublic'] = 1;
//            }else{
//                $data['employeeid'] = $userid;
//            }
//        }
        $sql = "select n.id,n.title,n.type,n.noticedate,n.noticetime,w.name,w.workaddress,w.address,w.number,ifnull(c.postcode,cc.postcode) as postcode,w.isneedaccommodation,"
                . "p.fltno,p.terminal,p.arrivetime,ifnull(o.position,w.position) as position,o.worktype,u.username "
                . "from lfsrlzy_notice n "
                . "left join lfsrlzy_workers w on n.association = w.id "
                . "left join lfsrlzy_profile pr on pr.workerid = w.id "
                . "left join lfsrlzy_pickup p on p.noticeid = n.id "
                . "left join lfsrlzy_order o on o.id = pr.ordersn "
                . "left join lfsrlzy_company c on c.id = o.companyid "
                . "left join lfsrlzy_company cc on cc.id = pr.companyid "
                . "left join lfsrlzy_user u on u.id = pr.saleser "
                . "where n.noticedate = '{$strtotime}'";
        if($ispickup){
            $sql .= " and n.type = 1";
        }else{
            $sql .= " and n.type != 1";
        }
        return $this->db->query($sql)->result_array();
    }
    
    public function new_reminder(){
        $data = array(
            'title'=>$this->input->post("title"),
            'comment'=>$this->input->post("comment"),
            'noticedate'=>strtotime($this->input->post("noticedate")),
            'noticetime'=>$this->input->post("noticetime"),
            'type'=>$this->input->post("type"),
            'association'=>$this->input->post("association"),
            'ispublic'=>$this->input->post("ispublic"),
            'employeeid'=>$this->session->userdata('user')->id,
            'createtime'=>time(),
            'updatetime'=>time()
        );
        $this->db->insert('notice',$data);
        return $this->db->insert_id();
    }
    
    public function new_pickup($id){
        $data = array(
            'fltno'=>$this->input->post("fltno"),
            'terminal'=>$this->input->post("terminal"),
            'arrivetime'=>strtotime($this->input->post("arrivetime")),
            'noticeid'=>$id
        );
        $this->db->insert('pickup',$data);
    }
    
    public function get5NoticeByDate($date,$userid,$type=-1){
        $str_sql  = "select n.*,w.name as workername from lfsrlzy_notice n left join lfsrlzy_workers w on n.association = w.id where n.noticedate = ".strtotime($date);
        if($type != -1){
            $str_sql  .= " and n.type = ".$type;
        }
        $str_sql  .= " order by n.noticetime asc limit 0,5";
//        if($userid*1 > 0){
//            $query = $this->db->get_where('employee', array('userid' => $userid));
//            $employee =  $query->row_array();
//            if($employee['department'] == 3){
//                $str_sql = "select n.*,w.name as workername from lfsrlzy_notice n "
//                        . "left join lfsrlzy_workers w on n.association = w.id "
//                        . "where n.noticedate = ".strtotime($date)." and n.ispublic = 1 "
//                        . "order by n.noticetime asc limit 0,5";
//            }else{
//                $str_sql = "select n.*,w.name as workername from lfsrlzy_notice n "
//                        . "left join lfsrlzy_workers w on n.association = w.id "
//                        . "where n.noticedate = ".strtotime($date)." and n.employeeid = ".$userid." "
//                        . "order by n.noticetime asc limit 0,5";
//            }
//        }
        return $this->db->query($str_sql)->result_array();
    }
    
    public function delete($id){
        $this->db->delete('notice', array('id' => $id));
        $this->db->delete('pickup', array('noticeid' => $id));
    }
    
    public function getNoticeByID($id){
        $str_sql  = "select n.*,w.id as workerid,w.name as workername,p.fltno,p.terminal,p.arrivetime from lfsrlzy_notice n "
                . "left join lfsrlzy_workers w on n.association = w.id "
                . "left join lfsrlzy_pickup p on n.id = p.noticeid "
                . "where n.id = ".$id;
        return $this->db->query($str_sql)->row_array();
    }
    
    public function update_reminder($id){
        $type = $this->input->post("type");
        $data = array(
            'title'=>$this->input->post("title"),
            'comment'=>$this->input->post("comment"),
            'noticedate'=>strtotime($this->input->post("noticedate")),
            'noticetime'=>$this->input->post("noticetime"),
            'ispublic'=>$this->input->post("ispublic"),
            'type'=>$type,
            'association'=>$this->input->post("association"),
            'updatetime'=>time()
        );
        $this->db->update('notice', $data, array('id' => $id));
        if($type*1 == 1){
            $data = array(
                'fltno'=>$this->input->post("fltno"),
                'terminal'=>$this->input->post("terminal"),
                'arrivetime'=>strtotime($this->input->post("arrivetime"))
            );
            $this->db->update('pickup', $data, array('noticeid' => $id));
        }
    }
    
    public function getNoticeByWorkerAndType($workerid,$type){
        $query = $this->db->get_where('notice', array('association' => $workerid,'type'=>$type));
        return $query->row_array();
    }
    
    public function getPickupByNoticeId($noticeid){
        $query = $this->db->get_where('pickup', array('noticeid'=>$noticeid));
        return $query->row_array();
    }
    
    public function get_summary($offset,$num,$searchData){
        $str_sql = "select p.id,ifnull(c.name,cc.name) as name,u.username,w.name workname,o.worktype,p.status,"
                . "p.wi,p.wici,e.name employeename,"
                . "p.createtime,p.applytime,p.approvetime,p.arrivedtime,p.issuetime,"
                . "p.returntime,p.rejectedtime,p.othertime,p.canceldate "
                . "from lfsrlzy_profile p "
                . "left join lfsrlzy_company c on c.id = p.companyid ";
        
        $str_sql .= "left join lfsrlzy_user u on u.id = p.saleser ";
        
        $str_sql .= "left join lfsrlzy_workers w on w.id = p.workerid ";
        $str_sql .= "left join lfsrlzy_employee e on e.userid = w.employeeid ";
        
        $str_sql .= "left join lfsrlzy_order o on o.id = p.ordersn ";
        
        $str_sql .= "left join lfsrlzy_company cc on cc.id = o.companyid ";
        
        $str_sql .= "where p.type != 5 ";
        if($searchData['condition'] != NULL && $searchData['condition'] != ""){
            $str_sql .= "and (c.name like '%{$searchData['condition']}%' or cc.name like '%{$searchData['condition']}%' "
            . "or w.name like '%{$searchData['condition']}%') ";
        }
        if($searchData['salesid'] != 0){
            $str_sql .= "and u.id = {$searchData['salesid']} ";
        }
        if($searchData['officerid'] != 0){
            $str_sql .= "and e.id = {$searchData['officerid']} ";
        }
        if($searchData['passtype'] != 0){
            $str_sql .= "and o.worktype = {$searchData['passtype']} ";
        }
        if($searchData['status'] != -1){
            if($searchData['searchstatus']==1){
                if($searchData['status'] == 0 || $searchData['status'] == 1 || $searchData['status'] == 2){
                    $str_sql .= "and p.status >= {$searchData['status']} ";
                }else if($searchData['status'] == 6){
                    $str_sql .= "and p.status >= 3 ";
                }else if($searchData['status'] == 3 || $searchData['status'] == 4){
                    $str_sql .= "and p.status >= {$searchData['status']} and p.status != 6 ";
                }else if($searchData['status'] == 7){
                    $str_sql .= "and (p.status = 5 or p.status = 7 or p.status = 8)";
                }else if($searchData['status'] == 8){
                    $str_sql .= "and (p.status = 5 or p.status = 8)";
                }else if($searchData['status'] == 5){
                    $str_sql .= "and p.status = {$searchData['status']} ";
                }
            }else{
                $str_sql .= "and p.status = {$searchData['status']} ";
            }
        }
        if($searchData['starttime'] != NULL && $searchData['starttime'] != ""){
            $searchData['starttime'] = strtotime($searchData['starttime']);
            if($searchData['searchstatus']==1){
                if($searchData['status']==1){
                    $str_sql .= "and p.applytime >= {$searchData['starttime']} ";
                }else if($searchData['status']==2){
                    $str_sql .= "and p.approvetime >= {$searchData['starttime']} ";
                }else if($searchData['status']==3){
                    $str_sql .= "and p.arrivedtime >= {$searchData['starttime']} ";
                }else if($searchData['status']==4){
                    $str_sql .= "and p.issuetime >= {$searchData['starttime']} ";
                }else if($searchData['status']==5){
                    $str_sql .= "and p.returntime >= {$searchData['starttime']} ";
                }else if($searchData['status']==6){
                    $str_sql .= "and p.rejectedtime >= {$searchData['starttime']} ";
                }else if($searchData['status']==7){
                    $str_sql .= "and p.othertime >= {$searchData['starttime']} ";
                }else if($searchData['status']==8){
                    $str_sql .= "and p.canceldate >= {$searchData['starttime']} ";
                }else{
                    $str_sql .= "and p.createtime >= {$searchData['starttime']} ";
                }
            }else{
                $str_sql .= "and p.createtime >= {$searchData['starttime']} ";
            }
        }
        if($searchData['endtime'] != NULL && $searchData['endtime'] != ""){
            $searchData['endtime'] = strtotime($searchData['endtime']);
            if($searchData['searchstatus']==1){
                if($searchData['status']==1){
                    $str_sql .= "and p.applytime <= {$searchData['endtime']} ";
                }else if($searchData['status']==2){
                    $str_sql .= "and p.approvetime <= {$searchData['endtime']} ";
                }else if($searchData['status']==3){
                    $str_sql .= "and p.arrivedtime <= {$searchData['endtime']} ";
                }else if($searchData['status']==4){
                    $str_sql .= "and p.issuetime <= {$searchData['endtime']} ";
                }else if($searchData['status']==5){
                    $str_sql .= "and p.returntime <= {$searchData['endtime']} ";
                }else if($searchData['status']==6){
                    $str_sql .= "and p.rejectedtime <= {$searchData['endtime']} ";
                }else if($searchData['status']==7){
                    $str_sql .= "and p.othertime <= {$searchData['endtime']} ";
                }else if($searchData['status']==8){
                    $str_sql .= "and p.canceldate <= {$searchData['endtime']} ";
                }else{
                    $str_sql .= "and p.createtime <= {$searchData['endtime']} ";
                }
            }else{
                $str_sql .= "and p.createtime <= {$searchData['endtime']} ";
            }
        }
        $totalData = $this->db->query($str_sql)->result_array();

        $str_sql .="order by {$searchData['ordername']} limit {$offset},{$num} ";
        $data = $this->db->query($str_sql)->result_array();
        return array(
            'total' => count($totalData),
            'res' => $data,
        );
    }

    public function get_summary_export($searchData){
        $str_sql = "select p.id,ifnull(c.name,cc.name) as name,u.username,w.name workname,o.worktype,p.status,"
                . "p.wi,p.wici,e.name employeename,"
                . "p.createtime,p.applytime,p.approvetime,p.arrivedtime,p.issuetime,"
                . "p.returntime,p.rejectedtime,p.othertime,p.canceldate "
                . "from lfsrlzy_profile p "
                . "left join lfsrlzy_company c on c.id = p.companyid ";
        
        $str_sql .= "left join lfsrlzy_user u on u.id = p.saleser ";
        
        $str_sql .= "left join lfsrlzy_workers w on w.id = p.workerid ";
        $str_sql .= "left join lfsrlzy_employee e on e.userid = w.employeeid ";
        
        $str_sql .= "left join lfsrlzy_order o on o.id = p.ordersn ";
        
        $str_sql .= "left join lfsrlzy_company cc on cc.id = o.companyid ";
        
        $str_sql .= "where p.type != 5 ";
        if($searchData['condition'] != NULL && $searchData['condition'] != ""){
            $str_sql .= "and (c.name like '%{$searchData['condition']}%' or cc.name like '%{$searchData['condition']}%' "
            . "or w.name like '%{$searchData['condition']}%') ";
        }
        if($searchData['salesid'] != 0){
            $str_sql .= "and u.id = {$searchData['salesid']} ";
        }
        if($searchData['officerid'] != 0){
            $str_sql .= "and e.id = {$searchData['officerid']} ";
        }
        if($searchData['passtype'] != 0){
            $str_sql .= "and o.worktype = {$searchData['passtype']} ";
        }
        if($searchData['status'] != -1){
            if($searchData['searchstatus']==1){
                if($searchData['status'] == 0 || $searchData['status'] == 1 || $searchData['status'] == 2){
                    $str_sql .= "and p.status >= {$searchData['status']} ";
                }else if($searchData['status'] == 6){
                    $str_sql .= "and p.status >= 3 ";
                }else if($searchData['status'] == 3 || $searchData['status'] == 4){
                    $str_sql .= "and p.status >= {$searchData['status']} and p.status != 6 ";
                }else if($searchData['status'] == 7){
                    $str_sql .= "and (p.status = 5 or p.status = 7 or p.status = 8)";
                }else if($searchData['status'] == 8){
                    $str_sql .= "and (p.status = 5 or p.status = 8)";
                }else if($searchData['status'] == 5){
                    $str_sql .= "and p.status = {$searchData['status']} ";
                }
            }else{
                $str_sql .= "and p.status = {$searchData['status']} ";
            }
        }
        if($searchData['starttime'] != NULL && $searchData['starttime'] != ""){
            $searchData['starttime'] = strtotime($searchData['starttime']);
            if($searchData['searchstatus']==1){
                if($searchData['status']==1){
                    $str_sql .= "and p.applytime >= {$searchData['starttime']} ";
                }else if($searchData['status']==2){
                    $str_sql .= "and p.approvetime >= {$searchData['starttime']} ";
                }else if($searchData['status']==3){
                    $str_sql .= "and p.arrivedtime >= {$searchData['starttime']} ";
                }else if($searchData['status']==4){
                    $str_sql .= "and p.issuetime >= {$searchData['starttime']} ";
                }else if($searchData['status']==5){
                    $str_sql .= "and p.returntime >= {$searchData['starttime']} ";
                }else if($searchData['status']==6){
                    $str_sql .= "and p.rejectedtime >= {$searchData['starttime']} ";
                }else if($searchData['status']==7){
                    $str_sql .= "and p.othertime >= {$searchData['starttime']} ";
                }else if($searchData['status']==8){
                    $str_sql .= "and p.canceldate >= {$searchData['starttime']} ";
                }else{
                    $str_sql .= "and p.createtime >= {$searchData['starttime']} ";
                }
            }else{
                $str_sql .= "and p.createtime >= {$searchData['starttime']} ";
            }
        }
        if($searchData['endtime'] != NULL && $searchData['endtime'] != ""){
            $searchData['endtime'] = strtotime($searchData['endtime']);
            if($searchData['searchstatus']==1){
                if($searchData['status']==1){
                    $str_sql .= "and p.applytime <= {$searchData['endtime']} ";
                }else if($searchData['status']==2){
                    $str_sql .= "and p.approvetime <= {$searchData['endtime']} ";
                }else if($searchData['status']==3){
                    $str_sql .= "and p.arrivedtime <= {$searchData['endtime']} ";
                }else if($searchData['status']==4){
                    $str_sql .= "and p.issuetime <= {$searchData['endtime']} ";
                }else if($searchData['status']==5){
                    $str_sql .= "and p.returntime <= {$searchData['endtime']} ";
                }else if($searchData['status']==6){
                    $str_sql .= "and p.rejectedtime <= {$searchData['endtime']} ";
                }else if($searchData['status']==7){
                    $str_sql .= "and p.othertime <= {$searchData['endtime']} ";
                }else if($searchData['status']==8){
                    $str_sql .= "and p.canceldate <= {$searchData['endtime']} ";
                }else{
                    $str_sql .= "and p.createtime <= {$searchData['endtime']} ";
                }
            }else{
                $str_sql .= "and p.createtime <= {$searchData['endtime']} ";
            }
        }
        $totalData = $this->db->query($str_sql)->result_array();

        $str_sql .="order by {$searchData['ordername']} ";
        $data = $this->db->query($str_sql)->result_array();
        return array(
            'total' => count($totalData),
            'res' => $data,
        );
    }
    
    public function get_invoice($offset,$num,$order='id desc',$condition){
        $table   = "lfsrlzy_invoice";
        $str_sql  = "select* from {$table} "
        . "where (client like '%".$condition."%' or no like '%".
                $condition."%' or invoicedate like '%".
                $condition."%' or salersperson like '%".
                $condition."%' or contact like '%".
                $condition."%' or shippeddate like '%".
                $condition."%') order by {$order}";
        $sql = $str_sql." limit {$offset},{$num}";
        return array(
            'total' => count($this->db->query($str_sql)->result_array()),
            'res' => $this->db->query($sql)->result_array(),
        ); 
    }
    
    public function newInvoice(){
        $data = array(
            'name'=>$this->input->post("name"),
            'address'=>$this->input->post("address"),
            'info'=>$this->input->post("info"),
            'bankaccount'=>$this->input->post("bankaccount"),
            'updatetime'=>time()
        );
        $this->db->update('selfinfo',$data,array('id' => 1));
        
        $data = array();
        $data['no'] = $this->input->post('no');
        $data['invoicedate'] = $this->input->post('invoicedate');
        $data['client'] = $this->input->post('client');
        $data['salersperson'] = $this->input->post('salersperson');
        $data['contact'] = $this->input->post('contact');
        $data['shippeddate'] = $this->input->post('shippeddate');
        $data['content'] = $this->input->post('content');
        $data['unitprice'] = $this->input->post('unitprice');
        $data['amountprice'] = $this->input->post('amountprice');
        $data['subtotal'] = $this->input->post('subtotal');
        $data['totaldue'] = $this->input->post('totaldue');
        $data['createtime'] = time();
        $data['updatetime'] = $data['createtime'];
        $this->db->insert('invoice', $data);
        return $this->db->insert_id();
    }
    
    public function getSelfInfo(){
        $query = $this->db->get("selfinfo");
        return $query->row_array();
    }
    
    public function deleteinvoice(){
        $id = $this->input->get("id");
        $this->db->delete('invoice', array('id' => $id));
    }
    
    public function getInvoice($id){
        $query = $this->db->get_where('invoice', array('id'=>$id));
        return $query->row_array();
    }
}

?>
